from typing import List, Dict, Any, Optional, Literal
from datetime import datetime
import sqlite3
from pydantic import BaseModel, Field, field_validator
from app.db.database import db

class LearnBase(BaseModel):
    """Learn基础模型"""
    learnId: str = Field(..., description="LearnID，格式：Learn-20251010-v123")
    process: str = Field(..., description="工序")
    procedure: str = Field(..., description="过程")
    subProcedure: str = Field(..., description="子过程")
    standardSolution: str = Field(..., description="标准方案")
    standardName: str = Field(..., description="标准名称")
    system: str = Field(..., description="体系")
    platform: str = Field(..., description="平台")
    lineType: str = Field(..., description="拉线类型")
    failureMode: str = Field(..., description="失效模式")
    schematicDiagram: str = Field(..., description="示意图")
    fixCateory: str = Field(..., description="固化类别")
    standardFileNo: str = Field(..., description="标准文件编号")
    controlStatus: str = Field(..., description="受控状态")
    landedStatus: str = Field(..., description="落地状态")
    subSystemRelatedItems: str = Field(..., description="子系统关联条目")
    creatorId: str = Field(..., description="创建人ID")
    updaterId: Optional[str] = Field(None, description="更新人ID")
    
    @field_validator('learnId')
    @classmethod
    def validate_learn_id(cls, v):
        if not v.startswith('Learn-'):
            raise ValueError('LearnID必须以Learn-开头')
        return v

class LearnCreate(LearnBase):
    """创建Learn模型"""
    pass

class LearnUpdate(BaseModel):
    """更新Learn模型"""
    learnId: Optional[str] = Field(None, description="LearnID")
    process: Optional[str] = Field(None, description="工序")
    procedure: Optional[str] = Field(None, description="过程")
    subProcedure: Optional[str] = Field(None, description="子过程")
    standardSolution: Optional[str] = Field(None, description="标准方案")
    standardName: Optional[str] = Field(None, description="标准名称")
    system: Optional[str] = Field(None, description="体系")
    platform: Optional[str] = Field(None, description="平台")
    lineType: Optional[str] = Field(None, description="拉线类型")
    failureMode: Optional[str] = Field(None, description="失效模式")
    schematicDiagram: Optional[str] = Field(None, description="示意图")
    fixCateory: Optional[str] = Field(None, description="固化类别")
    standardFileNo: Optional[str] = Field(None, description="标准文件编号")
    controlStatus: Optional[str] = Field(None, description="受控状态")
    landedStatus: Optional[str] = Field(None, description="落地状态")
    subSystemRelatedItems: Optional[str] = Field(None, description="子系统关联条目")
    updaterId: Optional[str] = Field(None, description="更新人ID")

class LearnResponse(LearnBase):
    """Learn响应模型"""
    id: int
    createTime: Optional[str] = Field(None, description="创建时间")
    updateTime: Optional[str] = Field(None, description="最近更新时间")
    
    class Config:
        from_attributes = True

class LearnWithLessonsResponse(LearnResponse):
    """包含关联课程的Learn响应模型"""
    lessons: List[dict] = Field(default_factory=list, description="关联的课程列表")

class Learn:
    """学习课程模型类"""
    
    def __init__(self, learnId: str, process: str, procedure: str, subProcedure: str,
                 standardSolution: str, standardName: str, system: str, platform: str,
                 lineType: str, failureMode: str, schematicDiagram: str, fixCateory: str,
                 standardFileNo: str, controlStatus: str, landedStatus: str,
                 subSystemRelatedItems: str, creatorId: str, updaterId: str = None,
                 learn_id: int = None):
        """
        初始化学习课程
        """
        self.id = learn_id
        self.learnId = learnId
        self.process = process
        self.procedure = procedure
        self.subProcedure = subProcedure
        self.standardSolution = standardSolution
        self.standardName = standardName
        self.system = system
        self.platform = platform
        self.lineType = lineType
        self.failureMode = failureMode
        self.schematicDiagram = schematicDiagram
        self.fixCateory = fixCateory
        self.standardFileNo = standardFileNo
        self.controlStatus = controlStatus
        self.landedStatus = landedStatus
        self.subSystemRelatedItems = subSystemRelatedItems
        self.creatorId = creatorId
        self.updaterId = updaterId
        self.createTime = None
        self.updateTime = None
    
    def save(self) -> int:
        """
        保存学习课程到数据库
        :return: 新插入记录的ID或现有记录的ID
        """
        if self.id:
            # 更新现有记录
            query = """
                UPDATE learn 
                SET learnId = ?, process = ?, procedure = ?, subProcedure = ?,
                    standardSolution = ?, standardName = ?, system = ?, platform = ?,
                    lineType = ?, failureMode = ?, schematicDiagram = ?, fixCateory = ?,
                    standardFileNo = ?, controlStatus = ?, landedStatus = ?,
                    subSystemRelatedItems = ?, updaterId = ?, updateTime = CURRENT_TIMESTAMP
                WHERE id = ?
            """
            params = (self.learnId, self.process, self.procedure, self.subProcedure,
                     self.standardSolution, self.standardName, self.system, self.platform,
                     self.lineType, self.failureMode, self.schematicDiagram, self.fixCateory,
                     self.standardFileNo, self.controlStatus, self.landedStatus,
                     self.subSystemRelatedItems, self.updaterId, self.id)
            db.execute_update(query, params)
            return self.id
        else:
            # 插入新记录
            query = """
                INSERT INTO learn (learnId, process, procedure, subProcedure,
                    standardSolution, standardName, system, platform, lineType,
                    failureMode, schematicDiagram, fixCateory, standardFileNo,
                    controlStatus, landedStatus, subSystemRelatedItems, creatorId, updaterId)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """
            params = (self.learnId, self.process, self.procedure, self.subProcedure,
                     self.standardSolution, self.standardName, self.system, self.platform,
                     self.lineType, self.failureMode, self.schematicDiagram, self.fixCateory,
                     self.standardFileNo, self.controlStatus, self.landedStatus,
                     self.subSystemRelatedItems, self.creatorId, self.updaterId)
            self.id = db.execute_insert(query, params)
            return self.id
    
    def delete(self) -> bool:
        """
        从数据库中删除学习课程
        :return: 是否删除成功
        """
        if not self.id:
            return False
        
        # 删除关联的课程关系
        db.execute_update("DELETE FROM learn_lesson WHERE learn_id = ?", (self.id,))
        
        # 删除学习课程
        return db.delete_by_id("learn", self.id)
    
    def add_lesson(self, lesson_id: int) -> bool:
        """
        添加关联的课程
        :param lesson_id: 课程ID
        :return: 是否添加成功
        """
        if not self.id:
            return False
        
        try:
            query = "INSERT INTO learn_lesson (learn_id, lesson_id) VALUES (?, ?)"
            db.execute_update(query, (self.id, lesson_id))
            return True
        except sqlite3.IntegrityError:
            # 关系已存在
            return False
    
    def remove_lesson(self, lesson_id: int) -> bool:
        """
        移除关联的课程
        :param lesson_id: 课程ID
        :return: 是否移除成功
        """
        if not self.id:
            return False
        
        query = "DELETE FROM learn_lesson WHERE learn_id = ? AND lesson_id = ?"
        affected_rows = db.execute_update(query, (self.id, lesson_id))
        return affected_rows > 0
    
    def get_lessons(self) -> List[Dict[str, Any]]:
        """
        获取关联的所有课程
        :return: 课程列表
        """
        if not self.id:
            return []
        
        query = """
            SELECT l.* FROM lesson l
            INNER JOIN learn_lesson ll ON l.id = ll.lesson_id
            WHERE ll.learn_id = ?
            ORDER BY l.id
        """
        return db.execute_query(query, (self.id,))
    
    def to_dict(self) -> Dict[str, Any]:
        """
        转换为字典格式
        :return: 字典格式的数据
        """
        return {
            'id': self.id,
            'learnId': self.learnId,
            'process': self.process,
            'procedure': self.procedure,
            'subProcedure': self.subProcedure,
            'standardSolution': self.standardSolution,
            'standardName': self.standardName,
            'system': self.system,
            'platform': self.platform,
            'lineType': self.lineType,
            'failureMode': self.failureMode,
            'schematicDiagram': self.schematicDiagram,
            'fixCateory': self.fixCateory,
            'standardFileNo': self.standardFileNo,
            'controlStatus': self.controlStatus,
            'landedStatus': self.landedStatus,
            'subSystemRelatedItems': self.subSystemRelatedItems,
            'creatorId': self.creatorId,
            'updaterId': self.updaterId,
            'createTime': self.createTime,
            'updateTime': self.updateTime
        }
    
    @classmethod
    def get_by_id(cls, learn_id: int) -> Optional['Learn']:
        """
        根据ID获取学习课程
        :param learn_id: 课程ID
        :return: Learn实例或None
        """
        data = db.get_by_id("learn", learn_id)
        if not data:
            return None
        
        learn = cls(
            learnId=data['learnId'],
            process=data['process'],
            procedure=data['procedure'],
            subProcedure=data['subProcedure'],
            standardSolution=data['standardSolution'],
            standardName=data['standardName'],
            system=data['system'],
            platform=data['platform'],
            lineType=data['lineType'],
            failureMode=data['failureMode'],
            schematicDiagram=data['schematicDiagram'],
            fixCateory=data['fixCateory'],
            standardFileNo=data['standardFileNo'],
            controlStatus=data['controlStatus'],
            landedStatus=data['landedStatus'],
            subSystemRelatedItems=data['subSystemRelatedItems'],
            creatorId=data['creatorId'],
            updaterId=data['updaterId'],
            learn_id=data['id']
        )
        learn.createTime = data['createTime']
        learn.updateTime = data['updateTime']
        return learn
    
    @classmethod
    def get_by_learn_id(cls, learn_id: str) -> Optional['Learn']:
        """
        根据LearnID获取学习课程
        :param learn_id: LearnID字符串
        :return: Learn实例或None
        """
        query = "SELECT * FROM learn WHERE learnId = ?"
        results = db.execute_query(query, (learn_id,))
        if not results:
            return None
        
        data = results[0]
        learn = cls(
            learnId=data['learnId'],
            process=data['process'],
            procedure=data['procedure'],
            subProcedure=data['subProcedure'],
            standardSolution=data['standardSolution'],
            standardName=data['standardName'],
            system=data['system'],
            platform=data['platform'],
            lineType=data['lineType'],
            failureMode=data['failureMode'],
            schematicDiagram=data['schematicDiagram'],
            fixCateory=data['fixCateory'],
            standardFileNo=data['standardFileNo'],
            controlStatus=data['controlStatus'],
            landedStatus=data['landedStatus'],
            subSystemRelatedItems=data['subSystemRelatedItems'],
            creatorId=data['creatorId'],
            updaterId=data['updaterId'],
            learn_id=data['id']
        )
        learn.createTime = data['createTime']
        learn.updateTime = data['updateTime']
        return learn
    
    @classmethod
    def get_all(cls, limit: int = None, offset: int = 0) -> List['Learn']:
        """
        获取所有学习课程
        :param limit: 限制数量
        :param offset: 偏移量
        :return: Learn实例列表
        """
        data_list = db.get_all("learn", limit, offset)
        learns = []
        
        for data in data_list:
            learn = cls(
                learnId=data['learnId'],
                process=data['process'],
                procedure=data['procedure'],
                subProcedure=data['subProcedure'],
                standardSolution=data['standardSolution'],
                standardName=data['standardName'],
                system=data['system'],
                platform=data['platform'],
                lineType=data['lineType'],
                failureMode=data['failureMode'],
                schematicDiagram=data['schematicDiagram'],
                fixCateory=data['fixCateory'],
                standardFileNo=data['standardFileNo'],
                controlStatus=data['controlStatus'],
                landedStatus=data['landedStatus'],
                subSystemRelatedItems=data['subSystemRelatedItems'],
                creatorId=data['creatorId'],
                updaterId=data['updaterId'],
                learn_id=data['id']
            )
            learn.createTime = data['createTime']
            learn.updateTime = data['updateTime']
            learns.append(learn)
        
        return learns
    
    @classmethod
    def search_by_learn_id(cls, learn_id: str) -> List['Learn']:
        """
        根据LearnID搜索学习课程
        :param learn_id: 搜索关键词
        :return: Learn实例列表
        """
        query = "SELECT * FROM learn WHERE learnId LIKE ? ORDER BY learnId"
        params = (f"%{learn_id}%",)
        data_list = db.execute_query(query, params)
        
        learns = []
        for data in data_list:
            learn = cls(
                learnId=data['learnId'],
                process=data['process'],
                procedure=data['procedure'],
                subProcedure=data['subProcedure'],
                standardSolution=data['standardSolution'],
                standardName=data['standardName'],
                system=data['system'],
                platform=data['platform'],
                lineType=data['lineType'],
                failureMode=data['failureMode'],
                schematicDiagram=data['schematicDiagram'],
                fixCateory=data['fixCateory'],
                standardFileNo=data['standardFileNo'],
                controlStatus=data['controlStatus'],
                landedStatus=data['landedStatus'],
                subSystemRelatedItems=data['subSystemRelatedItems'],
                creatorId=data['creatorId'],
                updaterId=data['updaterId'],
                learn_id=data['id']
            )
            learn.createTime = data['createTime']
            learn.updateTime = data['updateTime']
            learns.append(learn)
        
        return learns
    
    @classmethod
    def get_by_control_status(cls, control_status: str) -> List['Learn']:
        """
        根据受控状态获取学习课程
        :param control_status: 受控状态
        :return: Learn实例列表
        """
        query = "SELECT * FROM learn WHERE controlStatus = ? ORDER BY learnId"
        params = (control_status,)
        data_list = db.execute_query(query, params)
        
        learns = []
        for data in data_list:
            learn = cls(
                learnId=data['learnId'],
                process=data['process'],
                procedure=data['procedure'],
                subProcedure=data['subProcedure'],
                standardSolution=data['standardSolution'],
                standardName=data['standardName'],
                system=data['system'],
                platform=data['platform'],
                lineType=data['lineType'],
                failureMode=data['failureMode'],
                schematicDiagram=data['schematicDiagram'],
                fixCateory=data['fixCateory'],
                standardFileNo=data['standardFileNo'],
                controlStatus=data['controlStatus'],
                landedStatus=data['landedStatus'],
                subSystemRelatedItems=data['subSystemRelatedItems'],
                creatorId=data['creatorId'],
                updaterId=data['updaterId'],
                learn_id=data['id']
            )
            learn.createTime = data['createTime']
            learn.updateTime = data['updateTime']
            learns.append(learn)
        
        return learns
    
    @classmethod
    def get_count(cls) -> int:
        """
        获取学习课程总数
        :return: 总数
        """
        return db.get_table_count("learn")
    
    def __str__(self):
        return f"Learn(id={self.id}, learnId='{self.learnId}', process='{self.process}')"
    
    def __repr__(self):
        return self.__str__()
